﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;


namespace Agile_E
{
    
    static class DatabaseHandler
    {
        private static MySqlConnection mySqlCon;
        private static MySqlCommand command;
        private static MySqlDataReader reader;
    
        /*
         * Simple query function for testing
         */
        
        #region Methods

        static void query(string queryString)
        {
        }

        /*  public static Car getCarbyMakeModel(string make, string model)
          {
              Car retrievedCar = new Car();

              command = mySqlCon.CreateCommand();

              if (make.Length != 0 && model.Length != 0)
              {
                  command.CommandText = "Select * From Vehicles where " +
                      "Make = " + make + "AND model = " + model;
              }
                  try{
                      reader = command.ExecuteReader();
                      while (reader.Read())
                      {
                          if (reader["V_Year"] == DBNull.Value) retrievedCar.Year = 0; else retrievedCar.Year = reader.GetInt32("V_Year");
                          if (reader["Make"] == DBNull.Value) retrievedCar.Make = string.Empty; else retrievedCar.Make = reader.GetString("Make");
                          if (reader["Model"] == DBNull.Value) retrievedCar.Model = string.Empty; else retrievedCar.Model = reader.GetString("Model");
                          if (reader["Type_ID"] == DBNull.Value) retrievedCar.VehicleTypeID = 0; else retrievedCar.VehicleTypeID = reader.GetInt16("Type_ID");
                          if (reader["Vehicle_Type"] == DBNull.Value) retrievedCar.VehicleType = string.Empty; else retrievedCar.VehicleType = reader.GetString("Vehicle_Type");
                          if (reader["VIN"] == DBNull.Value) retrievedCar.VIN_Number = string.Empty; else retrievedCar.VIN_Number = reader.GetString("VIN");
                          if (reader["Exterior_Color"] == DBNull.Value) retrievedCar.exColor = string.Empty; else retrievedCar.exColor = reader.GetString("Exterior_Color");
                          if (reader["Interior_Color"] == DBNull.Value) retrievedCar.inColor = string.Empty; else retrievedCar.inColor = reader.GetString("Interior_Color");
                          if (reader["V_Condition"] == DBNull.Value) retrievedCar.Condition = string.Empty; else retrievedCar.Condition = reader.GetString("V_Condition");
                          if (reader["Mileage"] == DBNull.Value) retrievedCar.Mileage = 0; else retrievedCar.Mileage = reader.GetInt32("Mileage");
                          if (reader["MSRP"] == DBNull.Value) retrievedCar.TagPrice = 0.00f; else retrievedCar.TagPrice = reader.GetFloat("MSRP");
                          if (reader["Min_Sale_Price"] == DBNull.Value) retrievedCar.LowestSalePrice = 0.00f; else retrievedCar.LowestSalePrice = reader.GetFloat("Min_Sale_Price");
                          if (reader["V_Engine"] == DBNull.Value) retrievedCar.EngineType = string.Empty; else retrievedCar.EngineType = reader.GetString("V_Engine");
                          if (reader["Fuel_Type"] == DBNull.Value) retrievedCar.fuelType = string.Empty; else retrievedCar.fuelType = reader.GetString("Fuel_Type");
                          if (reader["Drive_Type"] == DBNull.Value) retrievedCar.driveType = string.Empty; else retrievedCar.driveType = reader.GetString("Drive_Type");
                          if (reader["Horsepower"] == DBNull.Value) retrievedCar.Horsepower = 0; else retrievedCar.Horsepower = reader.GetInt32("Horsepower");
                          if (reader["Torque"] == DBNull.Value) retrievedCar.Torque = 0; else retrievedCar.Torque = reader.GetInt32("Torque");
                          if (reader["Transmission"] == DBNull.Value) retrievedCar.Transmission = string.Empty; else retrievedCar.Transmission = reader.GetString("Transmission");
                          if (reader["Fuel_Economy_City"] == DBNull.Value) retrievedCar.CityFE = 0; else retrievedCar.CityFE = reader.GetInt32("Fuel_Economy_City");
                          if (reader["Fuel_Economy_Highway"] == DBNull.Value) retrievedCar.HighwayFE = 0; else retrievedCar.HighwayFE = reader.GetInt32("Fuel_Economy_Highway");
                          if (reader["Features"] == DBNull.Value) retrievedCar.Features = string.Empty; else retrievedCar.Features = reader.GetString("Features");
                          if (reader["Warranty"] == DBNull.Value) retrievedCar.Warranty = string.Empty; else retrievedCar.Warranty = reader.GetString("Warranty");
                         // if (reader["Dealership"] == DBNull.Value) retrievedCar.at_dealer = null; else retrievedCar.at_dealer = getDealership("Dealership");
                          if (reader["Date_Received"] == DBNull.Value) retrievedCar.DateReceived = Convert.ToDateTime("1/1/1001"); else retrievedCar.DateReceived = reader.GetDateTime("Date_Received");
                      }
                      reader.Close();
                  }
                  catch (Exception ex) { }
                  return retrievedCar;
          }
        
          public static Car getCarByYear(int min, int max)
          {
              Car retrievedCar = new Car();

              if (max < min)
                  throw new System.ArgumentException("Highest year cannot be lower than lowest year", "original");


              command = mySqlCon.CreateCommand();

              if(min.ToString().Length  == 0)
                  command.CommandText = "Select * from Vehicles Where " +
                      "Year < " + max;
              else if(max.ToString().Length == 0)
                  command.CommandText = "Select * from Vehicles Where " +
                      "Year > " + min;
              else
                  command.CommandText = "Select * from Vehicles Where " +
                      "Year > " + min + "and Year < " + max;

              try
              {
                  reader = command.ExecuteReader();
                  while (reader.Read())
                  {
                      if (reader["V_Year"] == DBNull.Value) retrievedCar.Year = 0; else retrievedCar.Year = reader.GetInt32("V_Year");
                      if (reader["Make"] == DBNull.Value) retrievedCar.Make = string.Empty; else retrievedCar.Make = reader.GetString("Make");
                      if (reader["Model"] == DBNull.Value) retrievedCar.Model = string.Empty; else retrievedCar.Model = reader.GetString("Model");
                      if (reader["Type_ID"] == DBNull.Value) retrievedCar.VehicleTypeID = 0; else retrievedCar.VehicleTypeID = reader.GetInt16("Type_ID");
                      if (reader["Vehicle_Type"] == DBNull.Value) retrievedCar.VehicleType = string.Empty; else retrievedCar.VehicleType = reader.GetString("Vehicle_Type");
                      if (reader["VIN"] == DBNull.Value) retrievedCar.VIN_Number = string.Empty; else retrievedCar.VIN_Number = reader.GetString("VIN");
                      if (reader["Exterior_Color"] == DBNull.Value) retrievedCar.exColor = string.Empty; else retrievedCar.exColor = reader.GetString("Exterior_Color");
                      if (reader["Interior_Color"] == DBNull.Value) retrievedCar.inColor = string.Empty; else retrievedCar.inColor = reader.GetString("Interior_Color");
                      if (reader["V_Condition"] == DBNull.Value) retrievedCar.Condition = string.Empty; else retrievedCar.Condition = reader.GetString("V_Condition");
                      if (reader["Mileage"] == DBNull.Value) retrievedCar.Mileage = 0; else retrievedCar.Mileage = reader.GetInt32("Mileage");
                      if (reader["MSRP"] == DBNull.Value) retrievedCar.TagPrice = 0.00f; else retrievedCar.TagPrice = reader.GetFloat("MSRP");
                      if (reader["Min_Sale_Price"] == DBNull.Value) retrievedCar.LowestSalePrice = 0.00f; else retrievedCar.LowestSalePrice = reader.GetFloat("Min_Sale_Price");
                      if (reader["V_Engine"] == DBNull.Value) retrievedCar.EngineType = string.Empty; else retrievedCar.EngineType = reader.GetString("V_Engine");
                      if (reader["Fuel_Type"] == DBNull.Value) retrievedCar.fuelType = string.Empty; else retrievedCar.fuelType = reader.GetString("Fuel_Type");
                      if (reader["Drive_Type"] == DBNull.Value) retrievedCar.driveType = string.Empty; else retrievedCar.driveType = reader.GetString("Drive_Type");
                      if (reader["Horsepower"] == DBNull.Value) retrievedCar.Horsepower = 0; else retrievedCar.Horsepower = reader.GetInt32("Horsepower");
                      if (reader["Torque"] == DBNull.Value) retrievedCar.Torque = 0; else retrievedCar.Torque = reader.GetInt32("Torque");
                      if (reader["Transmission"] == DBNull.Value) retrievedCar.Transmission = string.Empty; else retrievedCar.Transmission = reader.GetString("Transmission");
                      if (reader["Fuel_Economy_City"] == DBNull.Value) retrievedCar.CityFE = 0; else retrievedCar.CityFE = reader.GetInt32("Fuel_Economy_City");
                      if (reader["Fuel_Economy_Highway"] == DBNull.Value) retrievedCar.HighwayFE = 0; else retrievedCar.HighwayFE = reader.GetInt32("Fuel_Economy_Highway");
                      if (reader["Features"] == DBNull.Value) retrievedCar.Features = string.Empty; else retrievedCar.Features = reader.GetString("Features");
                      if (reader["Warranty"] == DBNull.Value) retrievedCar.Warranty = string.Empty; else retrievedCar.Warranty = reader.GetString("Warranty");
                    //  if (reader["Dealership"] == DBNull.Value) retrievedCar.at_dealer = null; else retrievedCar.at_dealer = getDealership("Dealership");
                      if (reader["Date_Received"] == DBNull.Value) retrievedCar.DateReceived = Convert.ToDateTime("1/1/1001"); else retrievedCar.DateReceived = reader.GetDateTime("Date_Received");
                  }
                  reader.Close();
              }
              catch (Exception ex) { }

              return retrievedCar;
          }

        public static Car getCarbyMileage(int min, int max)
        {
            Car retrievedCar = new Car();
            if (max < min)
                throw new System.ArgumentException("Max mileage cannot be lower than minimum mileage", "original");

            command = mySqlCon.CreateCommand();

            if (min.ToString().Length == 0)
                command.CommandText = "Select * From Vehicles Where " +
                    "Mileage < " + max;
            else if (max.ToString().Length == 0)
                command.CommandText = "Select * From Vehicles Where " +
                    "Mileage > " + min;
            else
                command.CommandText = "Select * From Vehicles Where " +
                    "Mileage > " + min + "AND Mileage < " + max;

            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    if (reader["V_Year"] == DBNull.Value) retrievedCar.Year = 0; else retrievedCar.Year = reader.GetInt32("V_Year");
                    if (reader["Make"] == DBNull.Value) retrievedCar.Make = string.Empty; else retrievedCar.Make = reader.GetString("Make");
                    if (reader["Model"] == DBNull.Value) retrievedCar.Model = string.Empty; else retrievedCar.Model = reader.GetString("Model");
                    if (reader["Type_ID"] == DBNull.Value) retrievedCar.VehicleTypeID = 0; else retrievedCar.VehicleTypeID = reader.GetInt16("Type_ID");
                    if (reader["Vehicle_Type"] == DBNull.Value) retrievedCar.VehicleType = string.Empty; else retrievedCar.VehicleType = reader.GetString("Vehicle_Type");
                    if (reader["VIN"] == DBNull.Value) retrievedCar.VIN_Number = string.Empty; else retrievedCar.VIN_Number = reader.GetString("VIN");
                    if (reader["Exterior_Color"] == DBNull.Value) retrievedCar.exColor = string.Empty; else retrievedCar.exColor = reader.GetString("Exterior_Color");
                    if (reader["Interior_Color"] == DBNull.Value) retrievedCar.inColor = string.Empty; else retrievedCar.inColor = reader.GetString("Interior_Color");
                    if (reader["V_Condition"] == DBNull.Value) retrievedCar.Condition = string.Empty; else retrievedCar.Condition = reader.GetString("V_Condition");
                    if (reader["Mileage"] == DBNull.Value) retrievedCar.Mileage = 0; else retrievedCar.Mileage = reader.GetInt32("Mileage");
                    if (reader["MSRP"] == DBNull.Value) retrievedCar.TagPrice = 0.00f; else retrievedCar.TagPrice = reader.GetFloat("MSRP");
                    if (reader["Min_Sale_Price"] == DBNull.Value) retrievedCar.LowestSalePrice = 0.00f; else retrievedCar.LowestSalePrice = reader.GetFloat("Min_Sale_Price");
                    if (reader["V_Engine"] == DBNull.Value) retrievedCar.EngineType = string.Empty; else retrievedCar.EngineType = reader.GetString("V_Engine");
                    if (reader["Fuel_Type"] == DBNull.Value) retrievedCar.fuelType = string.Empty; else retrievedCar.fuelType = reader.GetString("Fuel_Type");
                    if (reader["Drive_Type"] == DBNull.Value) retrievedCar.driveType = string.Empty; else retrievedCar.driveType = reader.GetString("Drive_Type");
                    if (reader["Horsepower"] == DBNull.Value) retrievedCar.Horsepower = 0; else retrievedCar.Horsepower = reader.GetInt32("Horsepower");
                    if (reader["Torque"] == DBNull.Value) retrievedCar.Torque = 0; else retrievedCar.Torque = reader.GetInt32("Torque");
                    if (reader["Transmission"] == DBNull.Value) retrievedCar.Transmission = string.Empty; else retrievedCar.Transmission = reader.GetString("Transmission");
                    if (reader["Fuel_Economy_City"] == DBNull.Value) retrievedCar.CityFE = 0; else retrievedCar.CityFE = reader.GetInt32("Fuel_Economy_City");
                    if (reader["Fuel_Economy_Highway"] == DBNull.Value) retrievedCar.HighwayFE = 0; else retrievedCar.HighwayFE = reader.GetInt32("Fuel_Economy_Highway");
                    if (reader["Features"] == DBNull.Value) retrievedCar.Features = string.Empty; else retrievedCar.Features = reader.GetString("Features");
                    if (reader["Warranty"] == DBNull.Value) retrievedCar.Warranty = string.Empty; else retrievedCar.Warranty = reader.GetString("Warranty");
                  //  if (reader["Dealership"] == DBNull.Value) retrievedCar.at_dealer = null; else retrievedCar.at_dealer = getDealership("Dealership");
                    if (reader["Date_Received"] == DBNull.Value) retrievedCar.DateReceived = Convert.ToDateTime("1/1/1001"); else retrievedCar.DateReceived = reader.GetDateTime("Date_Received");
                }
                reader.Close();
            }
            catch (Exception ex) { }

            return retrievedCar;
        }
        
        public static Car getCarByPrice(string type, int min, int max)
        {
            Car retrievedCar = new Car();
            
            if (max < min)
                throw new System.ArgumentException("Max price cannot be lower than minimum price", "original");

            command = mySqlCon.CreateCommand();

            if (type.Equals("msrp"))
            {
                if (min.ToString().Length == 0)
                    command.CommandText = "Select * From Vehicles where " +
                        "MSRP < " + max;
                else if (max.ToString().Length == 0)
                    command.CommandText = "Select * From Vehicles where " +
                        "MSRP > " + min;
                else
                    command.CommandText = "Select * From Vehicles where " +
                        "MSRP > " + min + "AND MSRP < " + max;
            }

            if(type.Equals("sale"))
            {
                if (min.ToString().Length == 0)
                    command.CommandText = "Select * From Vehicles where " +
                        "Min_Sale_Price < " + max;
                else if (max.ToString().Length == 0)
                    command.CommandText = "Select * From Vehicles where " +
                        "Min_Sale_Price > " + min;
                else
                    command.CommandText = "Select * From Vehicles where " +
                        "Min_Sale_Price > " + min + "AND Min_Sale_Price < " + max;
            }

            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    if (reader["V_Year"] == DBNull.Value) retrievedCar.Year = 0; else retrievedCar.Year = reader.GetInt32("V_Year");
                    if (reader["Make"] == DBNull.Value) retrievedCar.Make = string.Empty; else retrievedCar.Make = reader.GetString("Make");
                    if (reader["Model"] == DBNull.Value) retrievedCar.Model = string.Empty; else retrievedCar.Model = reader.GetString("Model");
                    if (reader["Type_ID"] == DBNull.Value) retrievedCar.VehicleTypeID = 0; else retrievedCar.VehicleTypeID = reader.GetInt16("Type_ID");
                    if (reader["Vehicle_Type"] == DBNull.Value) retrievedCar.VehicleType = string.Empty; else retrievedCar.VehicleType = reader.GetString("Vehicle_Type");
                    if (reader["VIN"] == DBNull.Value) retrievedCar.VIN_Number = string.Empty; else retrievedCar.VIN_Number = reader.GetString("VIN");
                    if (reader["Exterior_Color"] == DBNull.Value) retrievedCar.exColor = string.Empty; else retrievedCar.exColor = reader.GetString("Exterior_Color");
                    if (reader["Interior_Color"] == DBNull.Value) retrievedCar.inColor = string.Empty; else retrievedCar.inColor = reader.GetString("Interior_Color");
                    if (reader["V_Condition"] == DBNull.Value) retrievedCar.Condition = string.Empty; else retrievedCar.Condition = reader.GetString("V_Condition");
                    if (reader["Mileage"] == DBNull.Value) retrievedCar.Mileage = 0; else retrievedCar.Mileage = reader.GetInt32("Mileage");
                    if (reader["MSRP"] == DBNull.Value) retrievedCar.TagPrice = 0.00f; else retrievedCar.TagPrice = reader.GetFloat("MSRP");
                    if (reader["Min_Sale_Price"] == DBNull.Value) retrievedCar.LowestSalePrice = 0.00f; else retrievedCar.LowestSalePrice = reader.GetFloat("Min_Sale_Price");
                    if (reader["V_Engine"] == DBNull.Value) retrievedCar.EngineType = string.Empty; else retrievedCar.EngineType = reader.GetString("V_Engine");
                    if (reader["Fuel_Type"] == DBNull.Value) retrievedCar.fuelType = string.Empty; else retrievedCar.fuelType = reader.GetString("Fuel_Type");
                    if (reader["Drive_Type"] == DBNull.Value) retrievedCar.driveType = string.Empty; else retrievedCar.driveType = reader.GetString("Drive_Type");
                    if (reader["Horsepower"] == DBNull.Value) retrievedCar.Horsepower = 0; else retrievedCar.Horsepower = reader.GetInt32("Horsepower");
                    if (reader["Torque"] == DBNull.Value) retrievedCar.Torque = 0; else retrievedCar.Torque = reader.GetInt32("Torque");
                    if (reader["Transmission"] == DBNull.Value) retrievedCar.Transmission = string.Empty; else retrievedCar.Transmission = reader.GetString("Transmission");
                    if (reader["Fuel_Economy_City"] == DBNull.Value) retrievedCar.CityFE = 0; else retrievedCar.CityFE = reader.GetInt32("Fuel_Economy_City");
                    if (reader["Fuel_Economy_Highway"] == DBNull.Value) retrievedCar.HighwayFE = 0; else retrievedCar.HighwayFE = reader.GetInt32("Fuel_Economy_Highway");
                    if (reader["Features"] == DBNull.Value) retrievedCar.Features = string.Empty; else retrievedCar.Features = reader.GetString("Features");
                    if (reader["Warranty"] == DBNull.Value) retrievedCar.Warranty = string.Empty; else retrievedCar.Warranty = reader.GetString("Warranty");
                    //if (reader["Dealership"] == DBNull.Value) retrievedCar.at_dealer = null; else retrievedCar.at_dealer = getDealership("Dealership");
                    if (reader["Date_Received"] == DBNull.Value) retrievedCar.DateReceived = Convert.ToDateTime("1/1/1001"); else retrievedCar.DateReceived = reader.GetDateTime("Date_Received");
                }
                reader.Close();
            }
            catch (Exception ex) { }
            return retrievedCar;
        }
        */

        public static Car getCarbyVIN(string VIN)
        {
            Car getCar = new Car();
            command = mySqlCon.CreateCommand();
            if(VIN != string.Empty)
                command.CommandText = "Select * from Vehicles v left outer join Vehicle_Types t on v.Type_ID = t.Type_ID"
                        + " where v.VIN = '" + VIN + "'";
            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    if (reader["V_ID"] == DBNull.Value) getCar.Car_ID = -1; else getCar.Car_ID = reader.GetInt32("V_ID");
                    if (reader["V_Year"] == DBNull.Value) getCar.Year = 0; else getCar.Year = reader.GetInt32("V_Year");
                    if (reader["Make"] == DBNull.Value) getCar.Make = string.Empty; else getCar.Make = reader.GetString("Make");
                    if (reader["Model"] == DBNull.Value) getCar.Model = string.Empty; else getCar.Model = reader.GetString("Model");
                    if (reader["Type_ID"] == DBNull.Value) getCar.VehicleTypeID = 0; else getCar.VehicleTypeID = reader.GetInt16("Type_ID");
                    if (reader["Vehicle_Type"] == DBNull.Value) getCar.VehicleType = string.Empty; else getCar.VehicleType = reader.GetString("Vehicle_Type");
                    if (reader["VIN"] == DBNull.Value) getCar.VIN_Number = string.Empty; else getCar.VIN_Number = reader.GetString("VIN");
                    if (reader["Exterior_Color"] == DBNull.Value) getCar.exColor = string.Empty; else getCar.exColor = reader.GetString("Exterior_Color");
                    if (reader["Interior_Color"] == DBNull.Value) getCar.inColor = string.Empty; else getCar.inColor = reader.GetString("Interior_Color");
                    if (reader["V_Condition"] == DBNull.Value) getCar.Condition = string.Empty; else getCar.Condition = reader.GetString("V_Condition");
                    if (reader["Mileage"] == DBNull.Value) getCar.Mileage = 0; else getCar.Mileage = reader.GetInt32("Mileage");
                    if (reader["MSRP"] == DBNull.Value) getCar.TagPrice = 0.00f; else getCar.TagPrice = reader.GetFloat("MSRP");
                    if (reader["Min_Sale_Price"] == DBNull.Value) getCar.LowestSalePrice = 0.00f; else getCar.LowestSalePrice = reader.GetFloat("Min_Sale_Price");
                    if (reader["V_Engine"] == DBNull.Value) getCar.EngineType = string.Empty; else getCar.EngineType = reader.GetString("V_Engine");
                    if (reader["Fuel_Type"] == DBNull.Value) getCar.fuelType = string.Empty; else getCar.fuelType = reader.GetString("Fuel_Type");
                    if (reader["Drive_Type"] == DBNull.Value) getCar.driveType = string.Empty; else getCar.driveType = reader.GetString("Drive_Type");
                    if (reader["Horsepower"] == DBNull.Value) getCar.Horsepower = 0; else getCar.Horsepower = reader.GetInt32("Horsepower");
                    if (reader["Torque"] == DBNull.Value) getCar.Torque = 0; else getCar.Torque = reader.GetInt32("Torque");
                    if (reader["Transmission"] == DBNull.Value) getCar.Transmission = string.Empty; else getCar.Transmission = reader.GetString("Transmission");
                    if (reader["Fuel_Economy_City"] == DBNull.Value) getCar.CityFE = 0; else getCar.CityFE = reader.GetInt32("Fuel_Economy_City");
                    if (reader["Fuel_Economy_Highway"] == DBNull.Value) getCar.HighwayFE = 0; else getCar.HighwayFE = reader.GetInt32("Fuel_Economy_Highway");
                    if (reader["Features"] == DBNull.Value) getCar.Features = string.Empty; else getCar.Features = reader.GetString("Features");
                    if (reader["Warranty"] == DBNull.Value) getCar.Warranty = string.Empty; else getCar.Warranty = reader.GetString("Warranty");
                    if (reader["S_ID"] == DBNull.Value) getCar.Status = -1; else getCar.Status = reader.GetInt32("S_ID");
                    //if (reader["Dealership"] == DBNull.Value) getCar.Dealership = string.Empty; else getCar.Dealership = reader.GetString("Dealership");
                    if (reader["D_ID"] == DBNull.Value) getCar.D_ID = -1; else getCar.D_ID = reader.GetInt32("D_ID");
                    if (reader["Date_Received"] == DBNull.Value) getCar.DateReceived = Convert.ToDateTime("1/1/1001"); else getCar.DateReceived = reader.GetDateTime("Date_Received");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
            }
            return getCar;
        }
        
        public static void updateCar(Car car) 
        {
            string gotMonth;
            string gotDay;
            string gotYear;
            string mySqlDate;

            gotMonth = car.DateReceived.Month.ToString();
            gotDay = car.DateReceived.Day.ToString();
            gotYear = car.DateReceived.Year.ToString();
            mySqlDate = gotYear + "-" + gotMonth + "-" + gotDay;

            command = mySqlCon.CreateCommand();

            command.CommandText = "Update Vehicles " +
                "Set V_Year = " + car.Year + ", " +
                "Make = '" + car.Make + "', " +
                "Model = '" + car.Model + "', " +
                "VIN = '" + car.VIN_Number + "', " +
                "Type_ID = " + car.VehicleTypeID + ", " +
                "Exterior_Color = '" + car.exColor + "', " +
                "Interior_Color = '" + car.inColor + "', " +
                "V_Condition = '" + car.Condition + "', " +
                "Mileage = " + car.Mileage + ", " +
                "MSRP = " + car.TagPrice + ", " +
                "Min_Sale_Price = " + car.LowestSalePrice + ", " +
                "V_Engine = '" + car.EngineType + "', " +
                "Fuel_Type = '" + car.fuelType + "', " +
                "Drive_Type = '" + car.driveType + "', " +
                "Horsepower = " + car.Horsepower + ", " +
                "Torque = " + car.Torque + ", " +
                "Transmission = '" + car.Transmission + "', " +
                "Fuel_Economy_City = " + car.CityFE + ", " +
                "Fuel_Economy_Highway = " + car.HighwayFE + ", " +
                "Features = '" + car.Features.Replace("'", "\\'") + "', " +
                "Warranty = '" + car.Warranty.Replace("'", "\\'") + "', " +
                "Date_Received = '" + mySqlDate + "', " +
                "S_ID = " + car.Status + ", " +
                "D_ID = " + car.D_ID + " " +
                //"Dealership = '" + car.at_dealer.ContactInformation.FirstName + "' " +
                "Where V_ID = " + car.Car_ID;

            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex) {}
        }

        public static bool addCar(Car newCar)
        {
            if (doesVINExist(newCar.VIN_Number))
                return false;
            else
            {
                command = mySqlCon.CreateCommand();
                string DRMonth, DRDate, DRYear, mySqlDate;
                DRMonth = newCar.DateReceived.Month.ToString();
                DRDate = newCar.DateReceived.Day.ToString();
                DRYear = newCar.DateReceived.Year.ToString();
                mySqlDate = DRYear + "-" + DRMonth + "-" + DRDate;
                command.CommandText = "INSERT INTO Vehicles(V_Year, Make, Model, VIN, Type_ID, Exterior_Color, Interior_Color, V_Condition, Mileage, MSRP, " +
                "Min_Sale_Price, V_Engine, Fuel_Type, Drive_Type, Horsepower, Torque, Transmission, Fuel_Economy_City, Fuel_Economy_Highway, " +
                "Features, Warranty, D_ID, Date_Received, S_ID) " +
                    "Values(" + newCar.Year +
                    ", '" + newCar.Make + "', '" + newCar.Model +
                    "', '" + newCar.VIN_Number + "', " + newCar.VehicleTypeID +
                    ", '" + newCar.exColor + "', '" + newCar.inColor +
                    "', '" + newCar.Condition + "', " + newCar.Mileage +
                    ", " + newCar.TagPrice + ", " + newCar.LowestSalePrice +
                    ", '" + newCar.EngineType + "', '" + newCar.fuelType +
                    "', '" + newCar.driveType + "', " + newCar.Horsepower +
                    ", " + newCar.Torque + ", '" + newCar.Transmission +
                    "', " + newCar.CityFE + ", " + newCar.HighwayFE +
                    ", '" + newCar.Features.Replace("'", "\\'") + "', '" + newCar.Warranty.Replace("'", "\\'") +
                    "', " + newCar.D_ID +
                    ", '" + mySqlDate + "', " + newCar.Status + ")";

                try
                {
                    reader = command.ExecuteReader();
                    reader.Close();
                }
                catch (Exception ex)
                {
                }
                return true;
            }
        }

        public static void removeCar(Car removeCar)
        {

            command = mySqlCon.CreateCommand();

            command.CommandText = "Delete FROM Vehicles " +
                "WHERE VIN = '" + removeCar.VIN_Number + "' ";

            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex)
            {
            }
        }
        public static bool carPending(PendingCar pCar)
        {
            try
            {
                command.CommandText = "Insert into Pending_Sales(Seller, E_ID, V_Year, Make, Model, VIN, Agreed_Price, C_ID) "+
                    "Values('" + pCar.Seller + "', " + pCar.E_ID + ", " + pCar.Year + ", '" + pCar.Make + "', '" +
                    pCar.Model + "', '" + pCar.VIN +"', '" + pCar.Price + "', " + pCar.C_ID + ")";

                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex)
            {
            }

            return true;
        }
        public static Customer getCustomer(int customer_id) 
        {
            Customer reCustomer = new Customer();
            command = mySqlCon.CreateCommand();
            command.CommandText = "Select * From Customers where C_ID = " + customer_id;

            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    reCustomer.Customer_ID = reader.GetInt32("C_ID");
                    if (reader["First_Name"] == DBNull.Value) reCustomer.Info.FirstName = string.Empty; else reCustomer.Info.FirstName = reader.GetString("First_Name");
                    if (reader["Middle_Name"] == DBNull.Value) reCustomer.Info.MiddleName = string.Empty; else reCustomer.Info.MiddleName = reader.GetString("Middle_Name");
                    if (reader["Last_Name"] == DBNull.Value) reCustomer.Info.FirstName = string.Empty; else reCustomer.Info.LastName = reader.GetString("Last_Name");
                    if (reader["Home_Phone"] == DBNull.Value) reCustomer.Info.Homephone = string.Empty; else  reCustomer.Info.Homephone = reader.GetString("Home_Phone");
                    if (reader["Cell_Phone"] == DBNull.Value) reCustomer.Info.Cell = string.Empty; else reCustomer.Info.Cell = reader.GetString("Cell_Phone");
                    if (reader["Work_Phone"] == DBNull.Value) reCustomer.Info.Workphone = string.Empty; else reCustomer.Info.Workphone = reader.GetString("Work_Phone");
                    if (reader["Address"] == DBNull.Value) reCustomer.Info.Address = string.Empty; else reCustomer.Info.Address = reader.GetString("Address");
                    if (reader["City"] == DBNull.Value) reCustomer.Info.City = string.Empty; else reCustomer.Info.City = reader.GetString("City");
                    if (reader["State"] == DBNull.Value) reCustomer.Info.State = string.Empty; else reCustomer.Info.State = reader.GetString("State");
                    if (reader["Zip"] == DBNull.Value) reCustomer.Info.Zip = string.Empty; else reCustomer.Info.Zip = reader.GetString("Zip");
                    if (reader["Interested_in"] == DBNull.Value) reCustomer.Interested = string.Empty; else reCustomer.Interested = reader.GetString("Interested_in");
                    if (reader["Additional_notes"] == DBNull.Value) reCustomer.AdditionalInfo = string.Empty; else reCustomer.AdditionalInfo = reader.GetString("Additional_notes");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
            }
            return reCustomer;
        }
        
        public static void updateCustomer(Customer customer) 
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "Update Customers " +
                "Set Last_Name = '" + customer.Info.LastName + "', " +
                "Middle_Name = '" + customer.Info.MiddleName + "', " +
                "First_Name = '" + customer.Info.FirstName + "', " +
                "Home_Phone = '" + customer.Info.Homephone + "', " +
                "Cell_Phone = '" + customer.Info.Cell + "', " +
                "Work_Phone = '" + customer.Info.Workphone + "', " +
                "Address = '" + customer.Info.Address + "', " +
                "City = '" + customer.Info.City + "', " +
                "State = '" + customer.Info.State + "', " +
                "Zip = '" + customer.Info.Zip + "', " +
                "Interested_In = '" + customer.Interested + "', " +
                "Additional_Notes = '" + customer.AdditionalInfo + "' " +
                "Where C_ID = " + customer.Customer_ID;

            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex) { }
        }

        public static void addCustomer(Customer newCustomer)
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "Insert into Customers(Last_Name, Middle_Name, First_Name, Home_Phone, " +
            "Cell_Phone, Work_Phone, Address, City, State, Zip, Interested_in, Additional_notes)" +
                "Values('" + newCustomer.Info.LastName +
                "', '" + newCustomer.Info.MiddleName +
                "', '" + newCustomer.Info.FirstName +
                "', '" + newCustomer.Info.Homephone +
                "', '" + newCustomer.Info.Cell +
                "', '" + newCustomer.Info.Workphone +
                "', '" + newCustomer.Info.Address +
                "', '" + newCustomer.Info.City +
                "', '" + newCustomer.Info.State +
                "', '" + newCustomer.Info.Zip +
                "', '" + newCustomer.Interested +
                "', '" + newCustomer.AdditionalInfo + "')";
            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex)
            {
            }
        }

        public static void removeCustomer(Customer removedCustomer)
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "Delete from Customers " + 
                    "Where Customer_ID = " + removedCustomer.Customer_ID + " and " + 
                    "Last_Name ='" + removedCustomer.Info.LastName + 
                    "' AND First_Name = '" + removedCustomer.Info.FirstName + "'";
            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex)
            {
            }
        }
        public static Dealership getDealershipbyID(int id)
        {
            Dealership newDealership = new Dealership();
            command = mySqlCon.CreateCommand();

            command.CommandText = "select * from Dealerships where Dealership_ID = "
                + id + " ";
            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    newDealership.Dealership_ID = reader.GetInt32("Dealership_ID");
                    if (reader["Name"] == DBNull.Value) newDealership.ContactInformation.FirstName = string.Empty; else newDealership.ContactInformation.FirstName = reader.GetString("Name");
                    if (reader["Address"] == DBNull.Value) newDealership.ContactInformation.Address = string.Empty; else newDealership.ContactInformation.Address = reader.GetString("Address");
                    if (reader["City"] == DBNull.Value) newDealership.ContactInformation.City = string.Empty; else newDealership.ContactInformation.City = reader.GetString("City");
                    if (reader["State"] == DBNull.Value) newDealership.ContactInformation.State = string.Empty; else newDealership.ContactInformation.State = reader.GetString("State");
                    if (reader["Zip"] == DBNull.Value) newDealership.ContactInformation.Zip = string.Empty; else newDealership.ContactInformation.Zip = reader.GetString("Zip");
                    if (reader["Owner"] == DBNull.Value) newDealership.Owner = string.Empty; else newDealership.Owner = reader.GetString("Owner");
                    if (reader["Phone"] == DBNull.Value) newDealership.ContactInformation.Workphone = string.Empty; else newDealership.ContactInformation.Workphone = reader.GetString("Phone");
                    if (reader["Services"] == DBNull.Value) newDealership.Services = string.Empty; else newDealership.Services = reader.GetString("Services");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
            }

            return newDealership;
        }
        public static Dealership getDealership(string name)
        {
            Dealership newDealership = new Dealership();
            command = mySqlCon.CreateCommand();

            command.CommandText = "select * from Dealerships where Name LIKE '%"
                + name + "%'";
            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    newDealership.Dealership_ID = reader.GetInt32("Dealership_ID");
                    if (reader["Name"] == DBNull.Value) newDealership.ContactInformation.FirstName = string.Empty; else newDealership.ContactInformation.FirstName = reader.GetString("Name");
                    if (reader["Address"] == DBNull.Value) newDealership.ContactInformation.Address = string.Empty; else newDealership.ContactInformation.Address = reader.GetString("Address");
                    if (reader["City"] == DBNull.Value) newDealership.ContactInformation.City = string.Empty; else newDealership.ContactInformation.City = reader.GetString("City");
                    if (reader["State"] == DBNull.Value) newDealership.ContactInformation.State = string.Empty; else newDealership.ContactInformation.State = reader.GetString("State");
                    if (reader["Zip"] == DBNull.Value) newDealership.ContactInformation.Zip = string.Empty; else newDealership.ContactInformation.Zip = reader.GetString("Zip");
                    if (reader["Owner"] == DBNull.Value) newDealership.Owner = string.Empty; else newDealership.Owner = reader.GetString("Owner");
                    if (reader["Phone"] == DBNull.Value) newDealership.ContactInformation.Workphone = string.Empty; else newDealership.ContactInformation.Workphone = reader.GetString("Phone");
                    if (reader["Services"] == DBNull.Value) newDealership.Services = string.Empty; else newDealership.Services = reader.GetString("Services");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
            }

            return newDealership;
        }


        public static void updateDealership(Dealership dealership) 
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "Update Dealerships " +
                "Set Name = '" + dealership.ContactInformation.FirstName + "', " +
                "Address = '" + dealership.ContactInformation.Address + "', " +
                "City = '" + dealership.ContactInformation.City + "', " +
                "State = '" + dealership.ContactInformation.State + "', " +
                "Zip = '" + dealership.ContactInformation.Zip + "', " +
                "Owner = '" + dealership.Owner + "', " +
                "Phone = '" + dealership.ContactInformation.Workphone + "', " +
                "Services = '" + dealership.Services + "' " +
                "Where Dealership_ID = " + dealership.Dealership_ID;
            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex) { }

        }


        public static void addDealership(Dealership newPlace) 
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "Insert into Dealerships(Name, Address, City, State, Zip, Owner, Phone, Services) " +
                "Values('" + newPlace.ContactInformation.FirstName +
                "', '" + newPlace.ContactInformation.Address +
                "', '" + newPlace.ContactInformation.City +
                "', '" + newPlace.ContactInformation.State +
                "', '" + newPlace.ContactInformation.Zip +
                "', '" + newPlace.Owner +
                "', '" + newPlace.ContactInformation.Workphone +
                "', '" + newPlace.Services + "')";
            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex) { }
        }

        public static void removeDealership(Dealership oldPlace) 
        {

            command = mySqlCon.CreateCommand();

            command.CommandText = "Delete from Dealerships " +
                "Where Dealership_ID = " + oldPlace.Dealership_ID;

            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex) { }
        }

        public static Employees getEmployeeByLogin(string name)
        {
            Employees emp = new Employees();
            command = mySqlCon.CreateCommand();
            command.CommandText = "Select * From Employees e left join Dealerships d on e.Dealership_ID = d.Dealership_ID "
                + "where e.Login_name = '" + name + "' ";

            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    emp.DB_E_ID = reader.GetInt32("E_ID");
                    if (reader["Last_Name"] == DBNull.Value) emp.Info.LastName = string.Empty; else emp.Info.LastName = reader.GetString("Last_Name");
                    if (reader["Middle_Name"] == DBNull.Value) emp.Info.MiddleName = string.Empty; else emp.Info.MiddleName = reader.GetString("Middle_Name");
                    if (reader["First_Name"] == DBNull.Value) emp.Info.FirstName = string.Empty; else emp.Info.FirstName = reader.GetString("First_Name");
                    if (reader["Employee_ID"] == DBNull.Value) emp.IDNumber = string.Empty; else emp.IDNumber = reader.GetString("Employee_ID");
                    if (reader["_Position"] == DBNull.Value) emp.Position = string.Empty; else emp.Position = reader.GetString("_Position");
                    if (reader["Supervisor"] == DBNull.Value) emp.Supervisor = string.Empty; else emp.Supervisor = reader.GetString("Supervisor");
                    if (reader["Home_Phone"] == DBNull.Value) emp.Info.Homephone = string.Empty; else emp.Info.Homephone = reader.GetString("Home_Phone");
                    if (reader["Work_Phone"] == DBNull.Value) emp.Info.Workphone = string.Empty; else emp.Info.Workphone = reader.GetString("Work_Phone");
                    if (reader["Cell_Phone"] == DBNull.Value) emp.Info.Cell = string.Empty; else emp.Info.Cell = reader.GetString("Cell_Phone");
                    if (reader["Specialty"] == DBNull.Value) emp.Specialty = string.Empty; else emp.Specialty = reader.GetString("Specialty");
                    if (reader["Name"] == DBNull.Value) emp.Dealership = string.Empty; else emp.Dealership = reader.GetString("Name");
                    if (reader["Dealership_ID"] == DBNull.Value) emp.Dealership_ID = -1; else emp.Dealership_ID = reader.GetInt32("Dealership_ID");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
            }
            return emp;
        }

        public static Employees getEmployeeByID(string id)
        {
            Employees emp = new Employees();
            command = mySqlCon.CreateCommand();

            command.CommandText = "Select * from Employees e left join Dealerships d on e.Dealership_ID = d.Dealership_ID"
                +   " where Employee_ID = '" + id + "'";

            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    emp.DB_E_ID = reader.GetInt32("E_ID");
                    if (reader["Last_Name"] == DBNull.Value) emp.Info.LastName = string.Empty; else emp.Info.LastName = reader.GetString("Last_Name");
                    if (reader["Middle_Name"] == DBNull.Value) emp.Info.MiddleName = string.Empty; else emp.Info.MiddleName = reader.GetString("Middle_Name");
                    if (reader["First_Name"] == DBNull.Value) emp.Info.FirstName = string.Empty; else emp.Info.FirstName = reader.GetString("First_Name");
                    if (reader["Employee_ID"] == DBNull.Value) emp.IDNumber = string.Empty; else emp.IDNumber = reader.GetString("Employee_ID");
                    if (reader["_Position"] == DBNull.Value) emp.Position = string.Empty; else emp.Position = reader.GetString("_Position");
                    if (reader["Supervisor"] == DBNull.Value) emp.Supervisor = string.Empty; else emp.Supervisor = reader.GetString("Supervisor");
                    if (reader["Home_Phone"] == DBNull.Value) emp.Info.Homephone = string.Empty; else emp.Info.Homephone = reader.GetString("Home_Phone");
                    if (reader["Work_Phone"] == DBNull.Value) emp.Info.Workphone = string.Empty; else emp.Info.Workphone = reader.GetString("Work_Phone");
                    if (reader["Cell_Phone"] == DBNull.Value) emp.Info.Cell = string.Empty; else emp.Info.Cell = reader.GetString("Cell_Phone");
                    if (reader["Specialty"] == DBNull.Value) emp.Specialty = string.Empty; else emp.Specialty = reader.GetString("Specialty");
                    if (reader["Name"] == DBNull.Value) emp.Dealership = string.Empty; else emp.Dealership = reader.GetString("Name");
                    if (reader["Dealership_ID"] == DBNull.Value) emp.Dealership_ID = -1; else emp.Dealership_ID = reader.GetInt32("Dealership_ID");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
            }
            return emp;
        }

        public static void addEmployee(Employees employee) 
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "INSERT INTO Employees(Last_name, Middle_name, First_name, Employee_ID, _Position, " +
                "Supervisor, Home_Phone, Work_Phone, Cell_Phone, Specialty, Dealership_ID)" +
                "Values('" + employee.Info.LastName +
                "', '" + employee.Info.MiddleName + "', '" + employee.Info.FirstName +
                "', '" + employee.IDNumber + "', '" + employee.Position +
                "', '" + employee.Supervisor+ "', '" + employee.Info.Homephone +
                "', '" + employee.Info.Workphone + "', '" + employee.Info.Cell +
                "', '" + employee.Specialty + "', " + employee.Dealership_ID + " )";

            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex)
            {
            }
        }

        public static void removeEmployee(Employees oldEmployee)
        {
           command = mySqlCon.CreateCommand();
           command.CommandText = "Delete FROM Employees " +
                "WHERE Employee_ID = '" + oldEmployee.IDNumber + "' " +
                "and E_ID = " + oldEmployee.DB_E_ID + " ";

            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex)
            {
            }
        }
        public static void updateEmployee(Employees e) 
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "Update Employees " +
                "Set Last_Name = '" + e.Info.LastName + "', " +
                "First_Name = '" + e.Info.FirstName + "', " +
                "Middle_Name = '" + e.Info.MiddleName + "', " +
                "Employee_ID = '" + e.IDNumber + "', " +
                "Home_Phone = '" + e.Info.Homephone + "', " +
                "Work_Phone = '" + e.Info.Workphone + "', " +
                "Cell_Phone = '" + e.Info.Cell + "', " +
                "Specialty = '" + e.Specialty + "', " +
                "Dealership_ID = " + e.Dealership_ID + " " +
                "Where E_ID = " + e.DB_E_ID;
            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex) { }
        }
            
        public static void connect()
        {
            string MyConString = "SERVER=database2.cs.tamu.edu;" + "DATABASE=gul-TerraNet;" + "UID=gul;" + "PASSWORD=terranet431;";
            mySqlCon = new MySqlConnection(MyConString);
            try
            {
                mySqlCon.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        
        public static void disconnect()
        {
            if (reader.Read())
                reader.Close();
            mySqlCon.Close();
        }

        public static void addPayPeriod(PayPeriod period)
        {
            DateTime date = period.StartDate;

            while (date != period.EndDate)
            {

                MySqlCommand command1 = mySqlCon.CreateCommand();
                command1.CommandText = "Select * from Employees";

                MySqlDataReader reader1 = command1.ExecuteReader();

                while(reader1.Read())
                {
                    command = mySqlCon.CreateCommand();
                    command.CommandText = "INSERT INTO PayPeriod(Employee_ID, Hours, Date, PayPeriodID)" + 
                        "Values(" +
                        reader1.GetInt32("Employee_ID") + "," +
                        "0.0" + "," +
                        date.Year + "/" + date.Month + "/" + date.Day + "," +
                        period.ID + ")";

                    command.ExecuteNonQuery();
                }

                reader1.Close();
                date.AddDays(1.0);
            }
        }


        public static void updatePayPeriod(Employees e, PayPeriod p)
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "Select * From Empyloees Where Employee_ID = " + e.DB_E_ID + 
                " AND PayPeriodID = " + p.ID;

            reader = command.ExecuteReader();

            int i = -1;
            while (reader.Read())
            {
                string t = "";

                for (i = 0; i < p.Dates.Count; i++)
                {
                    t = reader.GetString("Date");

                    char[] delims = new char[1];
                    delims[0] = '/';

                    string[] date = t.Split(delims);

                    int[] values = new int[3];

                    values[0] = Convert.ToInt32(date[0]);
                    values[1] = Convert.ToInt32(date[1]);
                    values[2] = Convert.ToInt32(date[2]);

                    if (values[0] == p.Dates[i].Year &&
                        values[1] == p.Dates[i].Month &&
                        values[2] == p.Dates[i].Day)
                    {
                        break;
                    }
                }

                if (i == p.Dates.Count)
                    return;

                float hours = p.Hours[i];

                MySqlCommand command2 = mySqlCon.CreateCommand();

                command2.CommandText = "Update PayPeriod " +
                    "Set Hours = '" + hours + "' " +
                    "Where Employee_ID = " + e.DB_E_ID + " AND " +
                    "PayPeriod = " + p.ID + " AND " +
                    "Date = " + t;

                command2.ExecuteNonQuery();
            }

            reader.Close();
        }


        public static PayPeriod getPayPeriod(Employees e, int PayPeriodID)
        {
            PayPeriod p;

            command = mySqlCon.CreateCommand();

            command.CommandText = "Select * From PayPeriod Where " +
                "Employee_ID = " + e.DB_E_ID + " AND " +
                "PayPeriodID = " + PayPeriodID;

            reader = command.ExecuteReader();

            List<DateTime> dates = new List<DateTime>();

            while (reader.Read())
            {
                string t = reader.GetString("Date");

                char[] delims = new char[1];
                delims[0] = '/';

                string[] date = t.Split(delims);

                int[] values = new int[3];

                values[0] = Convert.ToInt32(date[0]);
                values[1] = Convert.ToInt32(date[1]);
                values[2] = Convert.ToInt32(date[2]);

                DateTime dt = new DateTime(values[0], values[1], values[2]);

                dates.Add(dt);
            }

            dates.Sort();

            DateTime start = dates[0];
            DateTime end = dates[dates.Count - 1];

            p = new PayPeriod(start, end, dates.Count);

            reader.Close();

            return p;
        }


        private static bool doesVINExist(string vin)
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "Select * From Vehicles Where VIN = '" + vin+"'";

            reader = command.ExecuteReader();
        
                bool t = reader.HasRows;
 
            reader.Close();

            return t;
        }

        public static List<Car> pendingSalesFromDealership(string dealership)
        {
            List<Car> cars = new List<Car>();

            command = mySqlCon.CreateCommand();

            command.CommandText = "Select * From Vehicles Where Dealership = '" +
                dealership + "' AND S_ID = 3";

            reader = command.ExecuteReader();

            while (reader.Read())
            {

            }

            return cars;
        }

        #endregion
    }
}
